/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package mapping;
import java.sql.ResultSet;
import mapping.SqlLib;
import entreprise.Drug;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
/**
 *
 * @author Quoc-Khai
 */
public class DBDrug {
    public Drug getDrug(String drugID) throws SQLException{
        Drug drug = null;
        String selectQuery;
        selectQuery = "SELECT * FROM drug WHERE drugID="+drugID+"";        
        SqlLib connec = new SqlLib();
        if (connec.initConnection()) {
            ResultSet sqlResultSet = connec.getDataFromQuery(selectQuery);
//            ResultSetMetaData metaData = sqlResultSet.getMetaData();
//            System.out.println(metaData.getColumnCount());
//   
//            sqlResultSet.last();
//            System.out.println(sqlResultSet.getRow());
//            sqlResultSet.beforeFirst();
            
            while(sqlResultSet.next()) {
               drug=new Drug();
               drug.setDrugID(sqlResultSet.getString("drugID")); 
               drug.setDrugName(sqlResultSet.getString("drugName")); 
               drug.setDrugPrice(sqlResultSet.getFloat("drugPrice")); 
               drug.setDrugDescription(sqlResultSet.getString("drugDescription"));
            }
        } else {
            return drug;
//            System.out.println("Drug not found!");
        } 
        //TODO;
        return drug;
    }
    public boolean createDrug(Drug drug) throws SQLException{
        //TODO;
        String insertQuery;
        insertQuery = "INSERT INTO drug (drugName, drugPrice, drugDescription) VALUES ('" + drug.getDrugName()
                + "','" + drug.getDrugPrice() + "','" + drug.getDrugDescription() + "')";
        SqlLib connec = new SqlLib();
        
        if (connec.initConnection()) {
            connec.execQuery(insertQuery);
            System.out.println("Insert Drug success!");
            return true;
        } 
        return false;
    }
    public boolean updateDrug(Drug drug){
        //TODO
         String updateQuery;
        updateQuery = "UPDATE drug SET drugName='" + drug.getDrugName() + "', drugPrice=" + drug.getDrugPrice()
                +",drugDescription='" + drug.getDrugDescription()+ "' WHERE drugID='"+drug.getDrugID()+"'";
        
        SqlLib connec = new SqlLib();
        
        if (connec.initConnection()) {
            connec.execQuery(updateQuery);
            System.out.println("Update Drug success!");
            return true;
        } 
        return false;
    }
    public boolean deleteDrug(String drugID){
        //TODO
        String deleteQuery;
        deleteQuery = "DELETE FROM drug WHERE drugID='"+drugID+"'"; 
        SqlLib connec = new SqlLib();
        
        if (connec.initConnection()) {
            connec.execQuery(deleteQuery);
            System.out.println("Delete Drug success!");
            return true;
        } 
        return false;
    }
    public ArrayList<Drug> getDrugs(String drugName,int from, int numberOfRow){
        ArrayList<Drug> drugs = new ArrayList<>();        
        String selectQuery="";
        if(drugName.equals("")){
            selectQuery="select * from drug LIMIT "+from+","+numberOfRow;
        }else{
            selectQuery="select * from drug where drugName LIKE '%"+drugName+"%' LIMIT "+from+","+numberOfRow;
        }
        SqlLib connec = new SqlLib();
        if (connec.initConnection()) {
            ResultSet sqlResultSet = connec.getDataFromQuery(selectQuery);            
            try {
                while(sqlResultSet.next()) {
                    Drug drug=new Drug();
                    drug.setDrugID(sqlResultSet.getString("drugID"));
                    drug.setDrugName(sqlResultSet.getString("drugName"));
                    drug.setDrugPrice(sqlResultSet.getFloat("drugPrice"));
                    drug.setDrugDescription(sqlResultSet.getString("drugDescription"));
                    drugs.add(drug);
                }
            } catch (SQLException ex) {
                Logger.getLogger(DBDrug.class.getName()).log(Level.SEVERE, null, ex);
            }
        } else {
//            System.out.println("Drug not found!");
        } 
        return drugs;
    }
    public int getNumberOfDrugs(String drugName){    
        String selectQuery="";
        if(drugName.equals("")){
            selectQuery="select count(*) as RECORDCOUNT from drug";
        }else{
            selectQuery="select count(*) as RECORDCOUNT from drug where drugName LIKE '%"+drugName+"%'";
        }
        SqlLib connec = new SqlLib();
        if (connec.initConnection()) {
            ResultSet sqlResultSet = connec.getDataFromQuery(selectQuery);            
            try {
                connec.closeConnection();
                int numberOfRow=0;
                while(sqlResultSet.next()) {
                    numberOfRow=sqlResultSet.getInt("RECORDCOUNT");
                }
                return numberOfRow;
            } catch (SQLException ex) {
                Logger.getLogger(DBDrug.class.getName()).log(Level.SEVERE, null, ex);
            }
        } else {
//            System.out.println("Drug not found!");
            return 0;
        } 
        return 0;
    }    
}
